package edu.hawaii.myisern.model;

import java.util.ArrayList;
import java.util.List;

/**
 * Applies MyIsern specific data types to the database interface.
 * 
 * @author Randy Cox
 * 
 */
public class DatabaseOrganizations {

  private final String className = "DatabaseOrganizations";

  private Database db;

  /** Organization table name for production. */
  public static final String ORGANIZATION_TABLE = "ORGANIZATIONS";

  /**
   * Main organization table variables.
   */

  /** Organization table name to use. */
  private String organizationTable = ORGANIZATION_TABLE;

  /** Organization create table command. */
  private String organizationCreateTableSql;

  /** Organization drop table command. */
  private String organizationDropTableSql;

  /** Organization select command. */
  private String organizationSelectSql;

  /** Organization insert command. */
  private String organizationInsertSql;

  /** Organization update command. */
  private String organizationUpdateSql;

  /** Organization delete command. */
  private String organizationDeleteSql;

  /** Organization select count command. */
  private String organizationCountSql;

  /** Organization clear command. */
  private String organizationClearSql;

  /** Organization list command. */
  private String organizationListSql;

  /**
   * Main affiliatedResearchers table variables.
   */

  /** Affiliated researchers table name for production. */
  public final String AF_RESEARCHERS_TABLE = "AFFILIATEDRESEARCHERS";

  /** Affiliated researchers table name to use. */
  private String afResearchersTable = AF_RESEARCHERS_TABLE;

  /** Affiliated researchers create table command. */
  private String afResearchersCreateTableSql;

  /** Affiliated researchers drop table command. */
  private String afResearchersDropTableSql;

  /** Affiliated researchers select command. */
  private String afResearchersSelectSql;

  /** Affiliated researchers update command. */
  private String afResearchersInsertSql;

  /** Affiliated researchers select update command. */
  private String afResearchersDeleteSql;

  /** Affiliated researchers clear command. */
  private String afResearchersClearSql;

  /**
   * Main keywords table variables.
   */

  /** Affiliated researchers table name for production. */
  public final String KEYWORDS_TABLE = "RESEARCHKEYWORDS";

  /** Affiliated researchers table name to use. */
  private String keywordsTable = KEYWORDS_TABLE;

  /** Affiliated researchers create table command. */
  private String keywordsCreateTableSql;

  /** Affiliated researchers drop table command. */
  private String keywordsDropTableSql;

  /** Affiliated researchers select command. */
  private String keywordsSelectSql;

  /** Affiliated researchers update command. */
  private String keywordsInsertSql;

  /** Affiliated researchers select update command. */
  private String keywordsDeleteSql;

  /** Affiliated researchers clear command. */
  private String keywordsClearSql;

  /**
   * Constructor, initializes driver, if testing, database to test database.
   * 
   */
  public DatabaseOrganizations() {
    String methodName = ".DatabaseOrganizations(): ";
    Log.enter(className + methodName + "organization table = " + this.organizationTable);
    Log.enter(className + methodName + "affiliated research table = " + this.afResearchersTable);
    Log.enter(className + methodName + "keywords table = " + this.keywordsTable);
    db = new Database();
    setSqlStrings();
  }

  /**
   * Sets the sql string commands based on current table names.
   */
  public final void setSqlStrings() {

    /**
     * Organization sql commands.
     */

    // Organization create table sql command.
    this.organizationCreateTableSql = db.CREATE_TABLE + this.organizationTable + " ("
        + "id VARCHAR(255) PRIMARY KEY, " + "name VARCHAR(255), " + "type VARCHAR(255), "
        + "contact VARCHAR(255), " + "country VARCHAR(255), "
        + "researchDescription VARCHAR(255), " + "homePage VARCHAR(255))";

    // Organization drop table sql command.
    this.organizationDropTableSql = db.DROP_TABLE + this.organizationTable;

    // Organization select sql command.
    this.organizationSelectSql = db.SELECT_FROM + this.organizationTable + " WHERE id = ?";

    // Organization select update command.
    this.organizationInsertSql = db.INSERT_INTO + this.organizationTable
        + " (id, name, type, contact, country, researchDescription, homePage)"
        + " VALUES (?, ?, ?, ?, ?, ?, ?)";

    // Organization select update command.
    this.organizationUpdateSql = db.UPDATE + this.organizationTable
        + " SET name = ?, type = ?, contact = ?, country = ?, researchDescription = ?,"
        + " homePage = ? WHERE id = ?";

    // Organization delete command.
    this.organizationDeleteSql = db.DELETE_FROM + this.organizationTable + " WHERE id = ?";

    // Organization count command.
    this.organizationCountSql = db.SELECT_COUNT + this.organizationTable;

    // Organization delete command.
    this.organizationClearSql = db.DELETE_FROM + this.organizationTable;

    // Organization list command.
    this.organizationListSql = db.SELECT_FROM + this.organizationTable;

    /**
     * Affiliated researchers sql commands.
     */

    String whereIdEqual = " WHERE organization_id = ?";

    // Affiliated researcher create table command.
    this.afResearchersCreateTableSql = db.CREATE_TABLE + this.afResearchersTable + " ("
        + "organization_id VARCHAR(255), " + "researcher_name VARCHAR(255))";

    // Affiliated researcher drop table command.
    this.afResearchersDropTableSql = db.DROP_TABLE + this.afResearchersTable;

    // Affiliated researcher select command - joins with researcher table.
    this.afResearchersSelectSql = "SELECT researcher_name FROM " + this.afResearchersTable
        + whereIdEqual;

    // Affiliated researcher insert command.
    this.afResearchersInsertSql = db.INSERT_INTO + this.afResearchersTable
        + " (organization_id, researcher_name)" + " VALUES (?, ?)";

    // Affiliated researcher delete command.
    this.afResearchersDeleteSql = db.DELETE_FROM + this.afResearchersTable + whereIdEqual;

    // Affiliated researcher clear command.
    this.afResearchersClearSql = db.DELETE_FROM + this.afResearchersTable;

    /**
     * Keywords sql commands.
     */

    // Keywords create table command.
    this.keywordsCreateTableSql = db.CREATE_TABLE + this.keywordsTable + " ("
        + "organization_id VARCHAR(255), " + "keyword VARCHAR(255))";

    // Keywords drop table command.
    this.keywordsDropTableSql = db.DROP_TABLE + this.keywordsTable;

    // Keywords select command - joins with researcher table.
    this.keywordsSelectSql = "SELECT keyword FROM " + this.keywordsTable + whereIdEqual;

    // Keywords insert command.
    this.keywordsInsertSql = db.INSERT_INTO + this.keywordsTable + " (organization_id, keyword)"
        + " VALUES (?, ?)";

    // Keywords delete command.
    this.keywordsDeleteSql = db.DELETE_FROM + this.keywordsTable + whereIdEqual;

    // Keywords clear command.
    this.keywordsClearSql = db.DELETE_FROM + this.keywordsTable;
  }

  /**
   * Open and connect to database.
   */
  private void connect() {
    db.connect();
  }

  /**
   * Check and create all tables.
   */
  private void open() {
    createTables();
  }

  /**
   * Check if organization table exists.
   * 
   * @return true if table exists.
   */
  public Boolean hasTableOrganization() {
    connect();
    return (db.hasTable(this.organizationTable));
  }

  /**
   * Check if affiliated researchers table exists.
   * 
   * @return true if table exists.
   */
  public Boolean hasTableAfResearchers() {
    connect();
    return (db.hasTable(this.afResearchersTable));
  }

  /**
   * Check if affiliated researchers table exists.
   * 
   * @return true if table exists.
   */
  public Boolean hasTableKeywords() {
    connect();
    return (db.hasTable(this.keywordsTable));
  }

  /**
   * Checks if organization table exits, if not creates it.
   */
  public void createTables() {
    String methodName = ".createTables(): ";
    connect();

    // create organization table.
    if (hasTableOrganization()) {
      String msg = className + methodName + this.organizationTable + " found.";
      Log.enter(msg);
    }
    else {
      db.execute(this.organizationCreateTableSql);
      String msg = className + methodName + this.organizationTable + " not found.  Table created.";
      Log.enter(msg);
    }

    // create affiliated researchers table.
    if (hasTableAfResearchers()) {
      String msg = className + methodName + this.afResearchersTable + " found.";
      Log.enter(msg);
    }
    else {
      db.execute(this.afResearchersCreateTableSql);
      String msg = className + methodName + this.afResearchersCreateTableSql
          + " not found.  Table created.";
      Log.enter(msg);
    }
    // create keywords table.
    if (hasTableKeywords()) {
      String msg = className + methodName + this.keywordsTable + " found.";
      Log.enter(msg);
    }
    else {
      db.execute(this.keywordsCreateTableSql);
      String msg = className + methodName + this.keywordsCreateTableSql
          + " not found.  Table created.";
      Log.enter(msg);
    }
  }

  /**
   * Checks database to see if organization with id already exists.
   * 
   * @param orgId of organization record.
   * @return boolean if record exists.
   */
  public Boolean hasOrganization(String orgId) {
    String methodName = ".hasOrganizations(): ";
    connect();
    String id = Id.build(orgId);
    Boolean found = (get(id) != null);
    Log.enter(className + methodName + found.toString());
    return found;
  }

  /**
   * Retrieves organization from database.
   * 
   * @param orgId of organization to retrieve.
   * @return organization if found, null if not found.
   */
  public MyOrganization get(String orgId) {
    String methodName = ".get(): ";
    connect();
    open();

    // Make sure id is in correct format.
    String id = Id.build(orgId);

    // Get the organization record
    List<List<String>> results = db.query(this.organizationSelectSql, id);
    Boolean found = false;
    if (results.size() == 1) {
      String resultId = results.get(0).get(0);
      if (resultId.equalsIgnoreCase(id)) {
        found = true;
      }
    }
    Log.enter(className + methodName + found.toString());
    MyOrganization organization = null;
    if (found) {
      List<String> row = results.get(0);
      organization = new MyOrganization();
      organization.setName(row.get(1));
      organization.setType(row.get(2));
      organization.setContact(row.get(3));
      organization.setCountry(row.get(4));
      organization.setResearchDescription(row.get(5));
      organization.setHomePage(row.get(6));
    }

    // Get any affiliated researchers related to this organization.
    results = db.query(this.afResearchersSelectSql, id);
    for (List<String> row : results) {
      organization.addAffiliatedResearcher(row.get(0));
      Log.enter(className + methodName + row.get(0));
    }

    // Get any affiliated researchers related to this organization.
    results = db.query(this.keywordsSelectSql, id);
    for (List<String> row : results) {
      organization.addResearchKeyword(row.get(0));
      Log.enter(className + methodName + row.get(0));
    }

    return organization;
  }

  /**
   * Insert or update organization record into table.
   * 
   * @param organization record to save.
   * @return count of records updated.
   */
  public Integer save(MyOrganization organization) {
    assert (organization != null);
    assert (organization.validate());
    String methodName = ".save(): ";
    connect();
    open();

    // If organization is already in database, update it.
    Integer updateCount = 0;
    if (hasOrganization(organization.getId())) {
      updateCount = db.update(this.organizationUpdateSql, organization.getName(), organization
          .getType(), organization.getContact(), organization.getCountry(), organization
          .getResearchDescription(), organization.getHomePage(), organization.getId());
      Log.enter(className + methodName + updateCount.toString() + " updated.");
    }

    // If organization not in database, insert it.
    else {
      updateCount = db.update(this.organizationInsertSql, organization.getId(), organization
          .getName(), organization.getType(), organization.getContact(), organization.getCountry(),
          organization.getResearchDescription(), organization.getHomePage());
      Log.enter(className + methodName + updateCount.toString() + " inserted.");
    }

    // Deleted associated affiliated researchers.
    Integer count = db.update(this.afResearchersDeleteSql, organization.getId());
    Log.enter(className + methodName + count.toString() + " deleted.");

    // Insert associated affiliated researchers.
    for (String name : organization.getAffiliatedResearchers()) {
      db.update(this.afResearchersInsertSql, organization.getId(), name);
      Log.enter(className + methodName + "affiliated researcher " + name
          + " inserted for organization:" + organization.getId());
    }

    // Deleted keywords.
    count = db.update(this.keywordsDeleteSql, organization.getId());
    Log.enter(className + methodName + count.toString() + " deleted.");

    // Insert associated affiliated researchers.
    for (String name : organization.getResearchKeywords()) {
      db.update(this.keywordsInsertSql, organization.getId(), name);
      Log.enter(className + methodName + "keyword " + name + " inserted for organization:"
          + organization.getId());
    }

    return updateCount;
  }

  /**
   * Delete organization information from organizational and sub tables.
   * 
   * @param id organization record to delete.
   * @return count of records deleted.
   */
  public Integer delete(String id) {
    assert (id != null);
    String methodName = ".delete: ";
    connect();
    open();

    // Make sure id is in correct format.
    String org_id = Id.build(id);

    // If organization is already in database, delete it.
    Integer deleteCount = 0;
    if (hasOrganization(org_id)) {
      deleteCount = db.update(this.organizationDeleteSql, org_id);
      Log.enter(className + methodName + deleteCount.toString() + " deleted.");
    }
    else {
      Log.enter(className + methodName + "organization record not found - " + org_id);
    }

    // Delete associated affiliated researchers.
    Integer count = db.update(this.afResearchersDeleteSql, org_id);
    Log.enter(className + methodName + count.toString() + "deleted.");

    // Delete associated keywords.
    count = db.update(this.keywordsDeleteSql, org_id);
    Log.enter(className + methodName + count.toString() + "deleted.");

    return deleteCount;
  }

  /**
   * Drop organization table.
   */
  public void dropTables() {
    String methodName = ".dropTables(): ";
    connect();

    // Drop organization table if it exists.
    if (db.hasTable(this.organizationTable)) {
      db.execute(this.organizationDropTableSql);
      String msg = className + methodName + this.organizationTable + " found.  Table dropped.";
      Log.enter(msg);
    }
    else {
      String msg = className + methodName + this.organizationTable + " not found.  Doing nothing.";
      Log.enter(msg);
    }

    // Drop affiliated researcher table if it exists.
    if (db.hasTable(this.afResearchersTable)) {
      db.execute(this.afResearchersDropTableSql);
      String msg = className + methodName + this.afResearchersTable + " found.  Table dropped.";
      Log.enter(msg);
    }
    else {
      String msg = className + methodName + this.afResearchersTable + " not found.  Doing nothing.";
      Log.enter(msg);
    }

    // Drop keyword table if it exists.
    if (db.hasTable(this.keywordsTable)) {
      db.execute(this.keywordsDropTableSql);
      String msg = className + methodName + this.keywordsTable + " found.  Table dropped.";
      Log.enter(msg);
    }
    else {
      String msg = className + methodName + this.keywordsTable + " not found.  Doing nothing.";
      Log.enter(msg);
    }
  }

  /**
   * Clear all organization information from organizational and sub tables.
   */
  public void clear() {
    String methodName = ".clear(): ";
    connect();
    open();

    // If organization is already in database, delete it.
    Integer count = db.update(this.organizationClearSql);
    Log.enter(className + methodName + count.toString() + " cleared.");

    // Delete associated affiliated researchers.
    count = db.update(this.afResearchersClearSql);
    Log.enter(className + methodName + count.toString() + " cleared.");

    // Delete associated affiliated researchers.
    count = db.update(this.keywordsClearSql);
    Log.enter(className + methodName + count.toString() + " cleared.");
  }

  /**
   * Count of organizations in table.
   * 
   * @return count of records.
   */
  public Integer count() {
    String methodName = ".count() ";
    connect();
    open();

    // Get count of the organization records
    List<List<String>> results = db.query(this.organizationCountSql);
    Integer count = Integer.parseInt(results.get(0).get(0));
    Log.enter(className + methodName + count.toString());
    return count;
  }

  /**
   * List of records in table.
   * 
   * @return a list of records.
   */
  public List<MyOrganization> list() {
    connect();
    open();
    List<MyOrganization> list = new ArrayList<MyOrganization>();
    List<List<String>> results = db.query(this.organizationListSql);
    for (List<String> row : results) {
      Id id = new Id(row.get(1));
      MyOrganization item = this.get(id.toString());
      list.add(item);
    }
    return list;
  }
}
